package smartcrud.spring.models.rbac;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import smartcrud.common.orm.Page;
import smartcrud.common.orm.PropertyFilter;
import smartcrud.common.orm.jdbc.Sql;

@Repository
public class RoleDaoJdbcImpl implements RoleDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public Role createRole(final Role o) {
		final String sql = "insert into RBAC_ROLE(NAME,COMMENT) values (?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(/* sql, */new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql,
						Statement.RETURN_GENERATED_KEYS);
				int z = 0;
				ps.setString(++z, o.getName());
				ps.setString(++z, o.getComment());
				return ps;
			}
		}, keyHolder);
		o.setId(keyHolder.getKey().longValue());
		return o;
	}
	
	@Override
	public void assignRoleWithMenus(Role role){
		List batchArgs = new ArrayList();
		if (role.getMenus()!=null) {
			for (Menu menu : role.getMenus()) {
				Object[] args = new Object[] { role.getId(), menu.getId() };
				batchArgs.add(args);
			}
		}
		//remove all
		jdbcTemplate.update("delete from rbac_role_menu where role_id=?", role.getId());

		//assign again
		String sql = "insert into rbac_role_menu(role_id,menu_id) values(?,?)";
		jdbcTemplate.batchUpdate(sql, batchArgs);
	}

	@Override
	public void deleteRole(Long id) {
		jdbcTemplate.update("delete from RBAC_ROLE where ID=?", id);
	}

	@Override
	public void updateRole(Role o) {
		final String sql = "update RBAC_ROLE set NAME=?,COMMENT=? where ID=?";
		jdbcTemplate.update(sql, o.getName(), o.getComment(), o.getId());
	}

	@Override
	public Role findRoleById(Long id) {
		final String sql = "select ID,NAME,COMMENT from RBAC_ROLE where ID=?";
		Role role = jdbcTemplate.queryForObject(sql, new RowMapper<Role>() {
			@Override
			public Role mapRow(ResultSet rs, int rowNum) throws SQLException {
				Role o = new Role();
				o.setId(rs.getLong("ID"));
				o.setName(rs.getString("NAME"));
				o.setComment(rs.getString("COMMENT"));
				return o;
			}
		}, id);
		
		role.setMenus(new HashSet(findRoleMenus(id)));
		return role;
	}
	
	private List<Menu> findRoleMenus(Long roleId){
		String sql = "select m.* from RBAC_Menu m, RBAC_ROLE_MENU rm "
				+ "where m.ID = rm.MENU_ID  and rm.ROLE_ID=?";

		List<Menu> content = jdbcTemplate.query(sql, new RowMapper<Menu>() {
			@Override
			public Menu mapRow(ResultSet rs, int rowNum) throws SQLException {
				Menu r = new Menu();
				r.setId(rs.getLong("id"));
				r.setLevel(rs.getInt("level"));
				r.setPid(rs.getLong("pid"));
				r.setName(rs.getString("name"));
				r.setMenuOrder(rs.getInt("menu_order"));
				return r;
			}
		}, roleId);

		return content;
	}

	@Override
	public List<Role> findAllRoles() {
		final String sql = "select ID,NAME,COMMENT from RBAC_ROLE";
		return jdbcTemplate.query(sql, new RowMapper<Role>() {
			@Override
			public Role mapRow(ResultSet rs, int rowNum) throws SQLException {
				Role o = new Role();
				o.setId(rs.getLong("ID"));
				o.setName(rs.getString("NAME"));
				o.setComment(rs.getString("COMMENT"));
				return o;
			}
		});
	}

	@Override
	public int count(Page<Role> page, final List<PropertyFilter> filters) {
		Sql sql = new Sql("select count(*) from RBAC_ROLE where 1=1 ", page,
				filters);
		sql.build4count();
		return jdbcTemplate.queryForInt(sql.getSqlBody(), sql.getSqlParams());
	}

	@Override
	public Page<Role> findByPage(Page<Role> page,
			final List<PropertyFilter> filters) {
		if (page.isAutoCount()) {
			int totalCount = count(page, filters);
			page.setTotalCount(totalCount);
		}

		Sql sql = new Sql("select * from RBAC_ROLE where 1=1 ", page, filters);
		sql.build();

		List<Role> content = jdbcTemplate.query(sql.getSqlBody(),
				new RowMapper<Role>() {
					@Override
					public Role mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Role o = new Role();
						o.setId(rs.getLong("ID"));
						o.setName(rs.getString("NAME"));
						o.setComment(rs.getString("COMMENT"));
						return o;
					}
				}, sql.getSqlParams());

		page.setContent(content);
		return page;
	}

	//put your code here
	
	@Override
	public List<Role> findUserRoles(Long userId) {
		final String sql = "select ID,NAME,COMMENT from RBAC_ROLE r where ";
		return jdbcTemplate.query(sql, new RowMapper<Role>() {
			@Override
			public Role mapRow(ResultSet rs, int rowNum) throws SQLException {
				Role o = new Role();
				o.setId(rs.getLong("ID"));
				o.setName(rs.getString("NAME"));
				o.setComment(rs.getString("COMMENT"));
				return o;
			}
		});
	}

}
